package sm.test;

import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import oracle.jdbc.driver.OracleDriver;

public class FullTableCopy3 {

	public static void main(String[] args) {
		try {
			System.out.println("begin full table copy of cm3r: "
					+ System.currentTimeMillis());
			DriverManager.deregisterDriver(new OracleDriver());
			Connection cn = DriverManager.getConnection(
					"jdbc:oracle:thin:@16.157.129.142:1521:o11r2upg",
					"SC_OWNER", "openview");
			Statement st = cn.createStatement();

			st.execute("INSERT INTO CM3RA1_NEW (\"NUMBER\", RECORD_NUMBER, REVIEWER_CLASS) SELECT NUMBERPRGN,RECORD_NUMBER,REVIEWER_CLASS FROM CM3RA1");
			System.out
					.println("CM3RA1 complete: " + System.currentTimeMillis());

			st.execute("INSERT INTO CM3RA2_NEW (\"NUMBER\", RECORD_NUMBER, CURRENT_PENDING_GROUPS) SELECT NUMBERPRGN,RECORD_NUMBER,CURRENT_PENDING_GROUPS FROM CM3RA2");
			System.out
					.println("CM3RA2 complete: " + System.currentTimeMillis());

			ResultSet rs1 = st
					.executeQuery("SELECT \"NUMBERPRGN\",\"PAGE\",\"TOTAL_PAGES\",\"CATEGORY\",\"STATUS\",\"APPROVAL_STATUS\",\"REQUESTED_BY\",\"REQUEST_DEPT\",\"REQUEST_PHONE\",\"REQUEST_DATE\",\"ASSIGNED_TO\",\"ASSIGN_DEPT\",\"ASSIGN_PHONE\",\"ASSIGN_DATE\",\"COORDINATOR\",\"COORD_DEPT\",\"COORD_PHONE\",\"COORD_DATE\",\"PLANNED_START\",\"SYSTEM\",\"KEY_ITEM_AFFECTED\",\"PLANNED_END\",\"REASON\",\"DURATION\",\"CURRENT_PHASE\",\"RISK_ASSESSMENT\",\"IPL_REQUIRED\",\"LOCATION_CODE\",\"PRIORITY_CODE\",\"IPL_TYPE\",\"DATE_ENTERED\",\"OPERATOR\",\"LASTPRGN\",\"OPEN\",\"RESOLVED_PROBLEMS\",\"PREREQ_CHANGES\",\"COREQ_CHANGES\",\"ALERT_STAGE\",\"ORIG_DATE_ENTERED\",\"ORIG_OPERATOR\",\"BUSINESS_AREA\",\"BACKOUT_DURATION\",\"CLOSE_TIME\",\"FOREIGN_ID\",\"WORK_MANAGER\",\"SLA_ALERT1\",\"SLA_TARGET\",\"SLA_DEADLINE\",\"SKIP_PHASES\",\"EXTENSION\",\"STILL_TO_GO_SAVED\",\"ESTIMATE_APPROVED\",\"TYPE_LEVEL2\",\"COMPANY\",\"BRIEF_DESCRIPTION\",\"SUBCATEGORY\",\"NUMBER_STRING\",\"BILLTO\",\"BILLTYPE\",\"GL_NUMBER\",\"AGREEMENT_ID\",\"DESCRIPTION\",\"JUSTIFICATION\",\"BACKOUT_METHOD\",\"APPROVED_GROUPS\",\"APPROVED_DATES\",\"APPROVED_OPER\",\"APPROVED_ACTION\",\"APPROVALS_REQUIRED\",\"APPROVALS_REQ_SEQ\",\"APPROVED_REQ_SEQ\",\"CURRENT_REQ_SEQ\",\"APPROVE_DESC\",\"TYPE\",\"BACKUP_DEVICE\",\"MODEL\",\"VENDOR\",\"FIXED_ASSET_NO_\",\"MANUFACTURER\",\"CPU_INTERRUPTION\",\"PROGRAM_NAME\",\"OPERATING_SYSTEM\",\"MAINT_LEVEL\",\"LIBRARY_AFFECTED\",\"RELEASE_LEVEL\",\"DATA_SET_AFFECTED\",\"NETWORK_AFFECTED\",\"VERSION\",\"INSTALL_OR_REMOVE\",\"UPGRADE\",\"VTAM_NAME\",\"VTAM_PARENT\",\"PARENT\",\"PRODUCT_NO_\",\"LOGICAL_NAME\",\"LOCATION\",\"SERIAL_NO_\",\"JOBNAME\",\"MISC1\",\"MISC2\",\"MISC3\",\"MISC4\",\"MISC5\",\"MISC6\",\"MISC7\",\"MISC8\",\"MISC9\",\"MISC10\",\"GROUPPRGN\",\"DOWN_START\",\"DOWN_END\",\"RAM_CURRENT\",\"HARD_DISC_CURRENT\",\"LOCATION_CODE_CURRENT\",\"RAM_NEW\",\"HARD_DISC_NEW\",\"LOCATION_CODE_NEW\",\"SIZEPRGN\",\"INSTALL_DATE\",\"SCHED_OUTAGE_START\",\"SCHED_OUTAGE_END\",\"ACTUAL_OUTAGE_START\",\"ACTUAL_OUTAGE_END\",\"OUTAGE_COMMENTS\",\"RESCHED_OUTAGES\",\"CANCELLED_OUTAGES\",\"MOVE_FLAG\",\"ADD_FLAG\",\"CHANGE_FLAG\",\"ACCOUNT_TYPE\",\"ACCOUNT_ID\",\"USER_NAME\",\"MISC_ARRAY1\",\"MISC_ARRAY2\",\"CONTRACT_ID\",\"MISC_ARRAY3\",\"ERP_UNIQUE_ID\",\"ERP_DESCRIPTION\",\"ERP_DEVELOPMENT_SID\",\"ERP_DEVELOPMENT_CLIENT\",\"ERP_DEVELOPMENT_APPROVER\",\"ERP_DEVELOPMENT_GATEWAY_ID\",\"ERP_RELEASED\",\"ERP_TARGET_SID\",\"ERP_TARGET_CLIENT\",\"ERP_ID_REQUESTED\",\"ERP_INSTANCES\",\"CONTACT_COST_CENTRE\",\"ESTIMATE_UNITS\",\"ESTIMATE_DESCRIPTION\",\"ESTIMATE_PRICE\",\"ESTIMATE_DELIVERY\",\"ESTIMATE_BUDGET\",\"ESTIMATE_EFFORT\",\"ESTIMATE_GRADE\",\"ACTUAL_COST\",\"ACTUAL_UNITS\",\"ACTUAL_PRICE\",\"ACTUAL_GRADE\",\"CORP_STRUCTURE\",\"ASSET_COMMENTS\",\"SLA_BREACH\",\"NEXT_BREACH\",\"CI_DOWN\",\"ID\",\"KMDOCTYPE\",\"BACKOUT_COMMENTS\",\"TRAINING_PLAN\",empty_blob(),\"COMPLETION_CODE\",\"HOURS_WORKED\",\"CLOSING_COMMENTS\",\"PARTS\",\"LABOR\",\"SYSMODCOUNT\",\"SYSMODUSER\",\"SYSMODTIME\",\"CONTACT_LAST_NAME\",\"CONTACT_FIRST_NAME\",\"SEVERITY\",\"INITIAL_IMPACT\",\"RLM_COMPONENTS\",\"RELEASE_TYPE\" from CM3RM1");
			PreparedStatement ps1 = cn
					.prepareStatement("INSERT INTO CM3RM1_NEW (\"NUMBER\",\"PAGE\",\"TOTAL_PAGES\",\"CATEGORY\",\"STATUS\",\"APPROVAL_STATUS\",\"REQUESTED_BY\",\"REQUEST_DEPT\",\"REQUEST_PHONE\",\"REQUEST_DATE\",\"ASSIGNED_TO\",\"ASSIGN_DEPT\",\"ASSIGN_PHONE\",\"ASSIGN_DATE\",\"COORDINATOR\",\"COORD_DEPT\",\"COORD_PHONE\",\"COORD_DATE\",\"PLANNED_START\",\"SYSTEM\",\"KEY_ITEM_AFFECTED\",\"PLANNED_END\",\"REASON\",\"DURATION\",\"CURRENT_PHASE\",\"RISK_ASSESSMENT\",\"IPL_REQUIRED\",\"LOCATION_CODE\",\"PRIORITY_CODE\",\"IPL_TYPE\",\"DATE_ENTERED\",\"OPERATOR\",\"LAST\",\"OPEN\",\"RESOLVED_PROBLEMS\",\"PREREQ_CHANGES\",\"COREQ_CHANGES\",\"ALERT_STAGE\",\"ORIG_DATE_ENTERED\",\"ORIG_OPERATOR\",\"BUSINESS_AREA\",\"BACKOUT_DURATION\",\"CLOSE_TIME\",\"FOREIGN_ID\",\"WORK_MANAGER\",\"SLA_ALERT1\",\"SLA_TARGET\",\"SLA_DEADLINE\",\"SKIP_PHASES\",\"EXTENSION\",\"STILL_TO_GO_SAVED\",\"ESTIMATE_APPROVED\",\"TYPE_LEVEL2\",\"COMPANY\",\"BRIEF_DESCRIPTION\",\"SUBCATEGORY\",\"NUMBER_STRING\",\"BILLTO\",\"BILLTYPE\",\"GL_NUMBER\",\"AGREEMENT_ID\",\"DESCRIPTION\",\"JUSTIFICATION\",\"BACKOUT_METHOD\",\"APPROVED_GROUPS\",\"APPROVED_DATES\",\"APPROVED_OPER\",\"APPROVED_ACTION\",\"APPROVALS_REQUIRED\",\"APPROVALS_REQ_SEQ\",\"APPROVED_REQ_SEQ\",\"CURRENT_REQ_SEQ\",\"APPROVE_DESC\",\"TYPE\",\"BACKUP_DEVICE\",\"MODEL\",\"VENDOR\",\"FIXED_ASSET_NO_\",\"MANUFACTURER\",\"CPU_INTERRUPTION\",\"PROGRAM_NAME\",\"OPERATING_SYSTEM\",\"MAINT_LEVEL\",\"LIBRARY_AFFECTED\",\"RELEASE_LEVEL\",\"DATA_SET_AFFECTED\",\"NETWORK_AFFECTED\",\"VERSION\",\"INSTALL_OR_REMOVE\",\"UPGRADE\",\"VTAM_NAME\",\"VTAM_PARENT\",\"PARENT\",\"PRODUCT_NO_\",\"LOGICAL_NAME\",\"LOCATION\",\"SERIAL_NO_\",\"JOBNAME\",\"MISC1\",\"MISC2\",\"MISC3\",\"MISC4\",\"MISC5\",\"MISC6\",\"MISC7\",\"MISC8\",\"MISC9\",\"MISC10\",\"GROUP\",\"DOWN_START\",\"DOWN_END\",\"RAM_CURRENT\",\"HARD_DISC_CURRENT\",\"LOCATION_CODE_CURRENT\",\"RAM_NEW\",\"HARD_DISC_NEW\",\"LOCATION_CODE_NEW\",\"SIZE\",\"INSTALL_DATE\",\"SCHED_OUTAGE_START\",\"SCHED_OUTAGE_END\",\"ACTUAL_OUTAGE_START\",\"ACTUAL_OUTAGE_END\",\"OUTAGE_COMMENTS\",\"RESCHED_OUTAGES\",\"CANCELLED_OUTAGES\",\"MOVE_FLAG\",\"ADD_FLAG\",\"CHANGE_FLAG\",\"ACCOUNT_TYPE\",\"ACCOUNT_ID\",\"USER_NAME\",\"MISC_ARRAY1\",\"MISC_ARRAY2\",\"CONTRACT_ID\",\"MISC_ARRAY3\",\"ERP_UNIQUE_ID\",\"ERP_DESCRIPTION\",\"ERP_DEVELOPMENT_SID\",\"ERP_DEVELOPMENT_CLIENT\",\"ERP_DEVELOPMENT_APPROVER\",\"ERP_DEVELOPMENT_GATEWAY_ID\",\"ERP_RELEASED\",\"ERP_TARGET_SID\",\"ERP_TARGET_CLIENT\",\"ERP_ID_REQUESTED\",\"ERP_INSTANCES\",\"CONTACT_COST_CENTRE\",\"ESTIMATE_UNITS\",\"ESTIMATE_DESCRIPTION\",\"ESTIMATE_PRICE\",\"ESTIMATE_DELIVERY\",\"ESTIMATE_BUDGET\",\"ESTIMATE_EFFORT\",\"ESTIMATE_GRADE\",\"ACTUAL_COST\",\"ACTUAL_UNITS\",\"ACTUAL_PRICE\",\"ACTUAL_GRADE\",\"CORP_STRUCTURE\",\"ASSET_COMMENTS\",\"SLA_BREACH\",\"NEXT_BREACH\",\"CI_DOWN\",\"ID\",\"KMDOCTYPE\",\"BACKOUT_COMMENTS\",\"TRAINING_PLAN\",\"SVC_OPTIONS\",\"COMPLETION_CODE\",\"HOURS_WORKED\",\"CLOSING_COMMENTS\",\"PARTS\",\"LABOR\",\"SYSMODCOUNT\",\"SYSMODUSER\",\"SYSMODTIME\",\"CONTACT_LAST_NAME\",\"CONTACT_FIRST_NAME\",\"SEVERITY\",\"INITIAL_IMPACT\",\"RLM_COMPONENTS\",\"RELEASE_TYPE\")");
			while (rs1.next()) {

			}

			System.out
					.println("CM3RM1 complete: " + System.currentTimeMillis());

			ResultSet rs = st
					.executeQuery("SELECT NUMBERPRGN, ASSETS FROM CM3RM1 where dbms_lob.getlength(ASSETS) > 0");

			PreparedStatement ps = cn
					.prepareStatement("INSERT INTO CM3RA3_NEW(\"NUMBER\", RECORD_NUMBER, ASSETS) values (?, ?, ?)");
			while (rs.next()) {
				String number = rs.getString(1);
				Clob assetClob = rs.getClob(2);
				int length = (int) assetClob.length();
				char[] chars = new char[length];
				Reader reader = assetClob.getCharacterStream();
				reader.read(chars);
				String assetStr = new String(chars);

				String[] assets = assetStr.split("\\n");
				for (int i = 0; i < assets.length; i++) {
					ps.setString(1, number);
					ps.setInt(2, i + 1);
					ps.setString(3, assets[i]);
					ps.addBatch();
				}
			}
			ps.executeBatch();
			System.out
					.println("CM3RA3 complete: " + System.currentTimeMillis());

			ps.close();
			cn.commit();
			st.close();
			cn.close();

			System.out.println("end full table copy of cm3r: "
					+ System.currentTimeMillis());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
